﻿using Microsoft.Data.Sqlite;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using WpfApp.Models;

namespace WpfApp.Services
{
    internal interface IWordService
    {
        /*辅助功能，基本的*/
        //查单词
        long GetWordId(string wordName);
        //查单词本
        int GetWordCountForWordbook(long wordbookId);

        /*单词本*/
        //加单词本
        public bool AddWordsToWordbookInDatabase(long wordbookId, List<Word> book);
        // 向某个单词本加入新的单词,单词本已经存在这个单词则不再加入
        bool AddWord(long userId, long wordbookId, Word word);

        /*特别功能，记忆相关*/
        // 更改单词本中某个单词的熟练度
        bool UpdateLearningProgress(long userId, long wordId, int newScore);
 /*       //用户定制的，按照条件查询到的单词
        List<Word> GetWordsFromWordbook(long wordbookId, long userId, int? scoreThreshold, bool? learned);*/
    }

    internal class WordService:IWordService
    {
        private string WordDatasetFilePath ;
        private string ConnectionString ;
        public WordService(string wordDatabaseFilePath)
        {
            WordDatasetFilePath = wordDatabaseFilePath;
        }
        public int GetWordCountForWordbook(long wordbookId)
        {
            int wordCount = 0;

             using (var connection = DatabaseHelper.GetDatabaseConnection(WordDatasetFilePath))
            {
                connection.Open();
                var query = @"
                SELECT COUNT(WordID)
                FROM Wordbook_Words
                WHERE WordbookID = @WordbookID
                ";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@WordbookID", wordbookId);
                    wordCount = Convert.ToInt32(command.ExecuteScalar());
                }
            }

            return wordCount;
        }
        public bool AddWord(long userId, long wordbookId, Word word)
        {
            try
            {
                if (WordExistsInWordbook(wordbookId, word.Name))
                {
                    return false; // 单词已经存在于该单词本中
                }
                return AddWordsToWordbookInDatabase(wordbookId, new List<Word> { word });
            }
            catch (Exception ex)
            {
                throw;

            }
        }

        public bool WordExistsInWordbook(long wordbookId, string wordName)
        {
             using (var connection = DatabaseHelper.GetDatabaseConnection(WordDatasetFilePath))
            {
                connection.Open();
                var query = @"
                SELECT COUNT(1)
                FROM Words w
                JOIN Wordbook_Words ww ON w.WordID = ww.WordID
                WHERE ww.WordbookID = @WordbookID AND w.Word = @Word";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@WordbookID", wordbookId);
                    command.Parameters.AddWithValue("@Word", wordName);
                    return Convert.ToInt32(command.ExecuteScalar()) > 0;
                }
            }
        }
        //合并释义
        private string MergeMeanings(string existingMeaning, string newMeaning)
        {
            var existingMeaningsList = JsonConvert.DeserializeObject<List<string>>(existingMeaning) ?? new List<string>();
            var newMeaningsList = JsonConvert.DeserializeObject<List<string>>(newMeaning) ?? new List<string>();

            // 合并并去重
            var mergedMeaningsList = existingMeaningsList.Union(newMeaningsList).ToList();

            return JsonConvert.SerializeObject(mergedMeaningsList);
        }

        public bool AddWordsToWordbookInDatabase(long wordbookId, List<Word> book)
        {
             using (var connection = DatabaseHelper.GetDatabaseConnection(WordDatasetFilePath)) 
            {
                connection.Open();
                using (var command = new SqliteCommand("PRAGMA foreign_keys = OFF;", connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        foreach (var word in book)
                        {
                            // 检查单词是否已存在
                            var selectWordQuery = @"
                        SELECT WordID, Meaning FROM Words WHERE Word = @Word;
                    ";
                            int wordId = 0;
                            string existingMeaning = null;

                            using (var command = new SqliteCommand(selectWordQuery, connection, transaction))
                            {
                                command.Parameters.AddWithValue("@Word", word.Name);
                                using (var reader = command.ExecuteReader())
                                {
                                    if (reader.Read())
                                    {
                                        wordId = reader.GetInt32(0);
                                        existingMeaning = reader.GetString(1);
                                    }
                                }
                            }

                            // 如果单词已存在，合并释义
                            if (wordId > 0)
                            {
                                var  meaning=JsonConvert.SerializeObject(word.Meaning);
                                var newMeaning = MergeMeanings(existingMeaning, meaning);
                                var updateWordQuery = @"
                                    UPDATE Words SET Meaning = @Meaning WHERE WordID = @WordID;
                                ";
                                using (var command = new SqliteCommand(updateWordQuery, connection, transaction))
                                {
                                    command.Parameters.AddWithValue("@Meaning", newMeaning);
                                    command.Parameters.AddWithValue("@WordID", wordId);
                                    command.ExecuteNonQuery();
                                }
                            }
                            else
                            {
                                // 如果单词不存在，插入新单词
                                var insertWordQuery = @"
                                    INSERT INTO Words (Word, Meaning)
                                    VALUES (@Word, @Meaning);
                                    SELECT last_insert_rowid();
                                ";
                                using (var command = new SqliteCommand(insertWordQuery, connection, transaction))
                                {
                                    command.Parameters.AddWithValue("@Word", word.Name);
                                    command.Parameters.AddWithValue("@Meaning", JsonConvert.SerializeObject(word.Meaning));
                                    wordId = Convert.ToInt32(command.ExecuteScalar());
                                }
                                // 插入关联到 Wordbook_Words 表
                                var insertWordbookWordsQuery = @"
                                    INSERT INTO Wordbook_Words (WordbookID, WordID)
                                    VALUES (@WordbookID, @WordID);
                                ";

                                using (var command = new SqliteCommand(insertWordbookWordsQuery, connection, transaction))
                                {
                                    command.Parameters.AddWithValue("@WordbookID", wordbookId);
                                    command.Parameters.AddWithValue("@WordID", wordId);
                                    command.ExecuteNonQuery();
                                }
                            }
                        }
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw;
                        
                    }

                }
            }
        }
        // 合并释义的辅助方法
/*        private Dictionary<string, string> MergeMeanings(Dictionary<string, string> existingMeaning, Dictionary<string, string> newMeaning)
        {
            foreach (var kvp in newMeaning)
            {
                if (existingMeaning.ContainsKey(kvp.Key))
                {
                    existingMeaning[kvp.Key] += "；" + kvp.Value;
                }
                else
                {
                    existingMeaning[kvp.Key] = kvp.Value;
                }
            }
            return existingMeaning;
        }*/
        public void RemoveDuplicatesAndMergeMeanings()
        {
            using (var connection = DatabaseHelper.GetDatabaseConnection(WordDatasetFilePath))
            {
                connection.Open();
                using (var command = new SqliteCommand("PRAGMA foreign_keys = OFF;", connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 1. 查找重复单词的ID列表
                        var selectDuplicatesQuery = @"
                            SELECT Word, GROUP_CONCAT(WordID) as WordIDs
                            FROM Words
                            GROUP BY Word
                            HAVING COUNT(*) > 1;
                        ";

                        var duplicates = new Dictionary<string, List<int>>();

                        using (var command = new SqliteCommand(selectDuplicatesQuery, connection, transaction))
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var word = reader.GetString(0);
                                var wordIds = reader.GetString(1)
                                                   .Split(',')
                                                   .Select(int.Parse)
                                                   .ToList();
                                duplicates.Add(word, wordIds);
                            }
                        }

                        // 2. 合并释义并处理重复
                        foreach (var entry in duplicates)
                        {
                            var word = entry.Key;
                            var wordIds = entry.Value;

                            // 获取所有重复单词的释义
                            var selectMeaningsQuery = @"
                                SELECT Meaning FROM Words WHERE WordID IN (" + string.Join(",", wordIds) + @");
                            ";

                            var allMeanings = new List<Dictionary<string,string>>();

                            using (var command = new SqliteCommand(selectMeaningsQuery, connection, transaction))
                            using (var reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    var meaning = reader.GetString(0);
                                    var dictionary = JsonConvert.DeserializeObject<Dictionary<string, string>>(meaning);
                                    // 移除值为空的键值对
                                    var cleanedDictionary = dictionary
                                        .Where(kv => !string.IsNullOrEmpty(kv.Value)) // 过滤掉值为空的项
                                        .GroupBy(kv => kv.Key) // 按键分组
                                        .ToDictionary(g => g.Key, g => g.First().Value); // 保留第一个出现的键值对

                                    // 如果清理后的字典不为空，则添加到列表中
                                    if (cleanedDictionary.Any())
                                    {
                                        allMeanings.Add(cleanedDictionary);
                                    }
                                }
                            }
                            /* var distinctMeanings = allMeanings
                                 .GroupBy(pair => pair.Values)
                                 .Select(group => group.First())  // 选择每个值组的第一个键值对
                                 .Distinct()                      // 确保最终字典的键值对是唯一的
                                 .ToDictionary(pair => pair.Keys, pair => pair.Values);*/
                            // 合并所有字典，并处理空值和重复键
                            var mergedDictionary = allMeanings
                                .SelectMany(dict => dict) // 将所有字典的键值对展开成一个序列
                                .Where(kv => !string.IsNullOrEmpty(kv.Value)) // 过滤掉值为空的项
                                .GroupBy(kv => kv.Key) // 按键分组
                                .ToDictionary(g => g.Key, g => g.First().Value); // 保留第一个出现的键值对
                            var mergedMeanings = JsonConvert.SerializeObject(mergedDictionary);

                            // 保留第一个WordID，更新释义
                            var mainWordId = wordIds.First();

                            var updateMeaningQuery = @"
                                UPDATE Words
                                SET Meaning = @Meaning
                                WHERE WordID = @WordID;
                            ";

                            using (var command = new SqliteCommand(updateMeaningQuery, connection, transaction))
                            {
                                command.Parameters.AddWithValue("@Meaning", mergedMeanings);
                                command.Parameters.AddWithValue("@WordID", mainWordId);
                                command.ExecuteNonQuery();
                            }

                            // 3. 删除Words里面其他重复记录
                            var deleteDuplicatesQuery = @"
                                DELETE FROM Words
                                WHERE WordID IN (" + string.Join(",", wordIds.Skip(1)) + @");
                            ";

                            using (var command = new SqliteCommand(deleteDuplicatesQuery, connection, transaction))
                            {
                                command.ExecuteNonQuery();
                            }

                            // 4. 更新Wordbook_Words中的引用
                            var getWordbookWordsQuery = @"
                                SELECT WordbookID
                                FROM Wordbook_Words
                                WHERE WordID IN (" + string.Join(",", wordIds) + @")
                            ";
                            var deletedWordbookWordsQuery = @"
                                DELETE FROM Wordbook_Words
                                WHERE WordID IN(" + string.Join(",", wordIds) + @") 
                                ";
                            var insertWordbookWordsQuery = @"
                                    INSERT INTO Wordbook_Words (WordID, WordbookID)
                                    VALUES (@MainWordID, @WordbookID)
                                ";
                            var wordbookIds = new List<int>();
                            using (var command = new SqliteCommand(getWordbookWordsQuery, connection, transaction))
                            {
                                using (var reader = command.ExecuteReader())
                                {
                                    while (reader.Read())
                                    {
                                        wordbookIds.Add(reader.GetInt32(0)); // Assuming WordbookID is the first column
                                    }
                                }
                            }
                            // 去重并转换回列表
                            wordbookIds = wordbookIds.Distinct().ToList();
                            // 2. 删除旧记录
                            using (var command = new SqliteCommand(getWordbookWordsQuery, connection, transaction))
                            {
                                command.ExecuteNonQuery();
                            }

                            // 3. 插入新记录
                            foreach (var wordbookId in wordbookIds)
                            {
                                using (var command = new SqliteCommand(getWordbookWordsQuery, connection, transaction))
                                {
                                    command.Parameters.AddWithValue("@MainWordID", mainWordId);
                                    command.Parameters.AddWithValue("@WordbookID", wordbookId);

                                    command.ExecuteNonQuery();
                                }
                            }
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        // 记录日志或处理异常
                        throw;
                    }
                }
            }
        }

        public DataTable GetWordsData(long wordbookId)
        {
            string query = @"
            SELECT w.WordID, w.Word, w.Meaning
            FROM Words w
            JOIN Wordbook_Words ww ON w.WordID = ww.WordID
            WHERE ww.WordbookID = @WordbookID";

            using (var connection = DatabaseHelper.GetDatabaseConnection(WordDatasetFilePath))
            {
                connection.Open();
                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@WordbookID", wordbookId);
                    using (var reader = command.ExecuteReader())
                    {
                        DataTable dataTable = new DataTable();
                        dataTable.Load(reader);
                        return dataTable;
                    }
                }
            }
        }

        
        public long GetWordId(string wordName)
        {
             using (var connection = DatabaseHelper.GetDatabaseConnection(WordDatasetFilePath))
            {
                connection.Open();
                var query = "SELECT WordID FROM Words WHERE Word = @Word";
                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Word", wordName);
                    var result = command.ExecuteScalar();
                    if (result == null)
                    {
                        throw new Exception($"GetWordId失败,单词{wordName}不存在.");
                    }
                    return (long)result;
                }
            }
        }
        public bool UpdateLearningProgress(long userId, long wordId, int newScore)
        {
             using (var connection = DatabaseHelper.GetDatabaseConnection(WordDatasetFilePath))
            {
                connection.Open();
                var query = @"
                UPDATE LearningProgress
                SET CorrectCount = @Score, LastReviewed = @LastReviewed
                WHERE UserID = @UserID AND WordID = @WordID";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Score", newScore);
                    command.Parameters.AddWithValue("@LastReviewed", DateTime.Now);
                    command.Parameters.AddWithValue("@UserID", userId);
                    command.Parameters.AddWithValue("@WordID", wordId);

                    return command.ExecuteNonQuery() > 0;
                }
            }
        }
        public bool DeleteWordbookWords(long wordbookId)
        {
            try {
                 using (var connection = DatabaseHelper.GetDatabaseConnection(WordDatasetFilePath))
                {
                    connection.Open();
                    var deleteWordbookWordsQuery = "DELETE FROM Wordbook_Words WHERE WordbookID = @WordbookID";
                    using (var command = new SqliteCommand(deleteWordbookWordsQuery, connection))
                    {
                        command.Parameters.AddWithValue("@WordbookID", wordbookId);
                        command.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch { 
                return false;
                throw;
            }
           
        }


    }
}
